Tabular Model - Cash Planning

Contents

• Usage/Purpose
• Fact Tables
• Dimension Tables
• Configurations
• Limitations
• Relationships

Usage/Purpose

The main purpose of the Cash Planning tabular model is to visualize the future cash position for a project, a company or for a group of companies. Depending on the purpose of the plan it can vary both in length and detail level. The cash position analysis can be based on several different dimensions such as Cash Plan Source, Cash Probability, Scenarios, Counterpart, Project, Cost Revenue Elements, Activities, Sub Projects, Subcontract and more. The base for the analysis is the cash flow date that can be grouped into calendar periods, accounting periods or user defined time buckets.

The tabular model contains three types of measures: “Cash flow” = Sum of opening balance, inflow and outflow transactions. “Inflow” = Sum of inflow transactions. “Outflow” = Sum of outflow transactions.

NOTICE: When using these measures is it mandatory to provide slicer to be able to select one (and only one) value in each of the two tables “SWITCH CURRENCY TYPE” and “SWITCH TAX”.

The measures also exist in different running total version stored in four separate display folders:

• “Accumulative By All Dates” = Gives a running total for all dates starting from the first available transaction. Dates without transaction are given the same value as the previous available date.
• “Accumulative By All Time buckets” = Gives a running total for all time buckets starting from the first available transaction. Time buckets without transaction are given the same value as the previous available time bucket.
• “Accumulative By Date” = Gives a running total only for those dates with available transactions in the Fact table.
• “Accumulative By Time bucket” = Gives a running total only for those time buckets with available transactions in the Fact table.

NOTICE: To minimize the risk of performance issues with these running totals, it is advisable to provide the user with the option to limit the time span that is analyzed.

In addition to the different cash flow measures to analyze, the tabular model also contains a set of supporting measures:

• “Transaction Exist Flag” = The value is “1” if fact table data exist for current filter selection. Can for example be used to downsize a slicer to only include applicable values.
• “Selected Amount Measure” = Determines which column(measure) from the Fact table to include in the “Cash flow” measure. [Hidden]
• “Selected Currency” = Determines which of the five available currency that has been selected. [Hidden]
• “Selected Tax” = Determines which of the five available currency that has been selected. [Hidden]
• “Selected Rep Curr 1” = The reporting currency 1 for the current cash plan. The value is retrieved from the CASH PLAN table.
• “Selected Rep Curr 2” = The reporting currency 2 for the current cash plan. The value is retrieved from the CASH PLAN table.
• “Selected Rep Curr 3” = The reporting currency 3 for the current cash plan. The value is retrieved from the CASH PLAN table.

Fact Tables

Model Table	DW Source view (SQL Server)	BI Access View	Referenced Information Source	Recommended Access Type
CASH PLAN MEASURES	FACT_CASH_PLAN_DETAIL_TM	FACT_CASH_PLAN_DETAIL_BI	FACT_CASH_PLAN_DETAIL	Data Mart

 Corresponding Information Sources are described below. Detailed infomation about Information Sources can be found in About Information Sources

Dimension Tables

Model Table	DW Source view (SQL Server)	BI Access View	Referenced Information Source	Recommended Access Type
ACCOUNTING CURRENCY	DIM_CURRENCY_CODE_TM	DIM_CURRENCY_CODE_BI	DIM_CURRENCY_CODE	On Line
ACCOUNTING PERIOD	DIM_ACCOUNTING_PERIOD_BI	DIM_ACCOUNTING_PERIOD_BI	DIM_ACCOUNTING_PERIOD	On Line
ACCOUNTING PROJECT	DIM_ACCOUNTING_PROJECT_BI	DIM_ACCOUNTING_PROJECT_BI	DIM_ACCOUNTING_PROJECT	On Line
CASH FLOW PERIOD	DIM_BI_TIME_BI	DIM_BI_TIME_BI	DIM_BI_TIME	On Line
CASH PLAN	DIM_CASH_PLAN_TM	DIM_CASH_PLAN_BI	DIM_CASH_PLAN	On Line
CASH PLAN CURRENCY	CAPL_CURRENCY_TM
CASH PLAN DETAILS	FACT_CASH_PLAN_DETAIL_TM	FACT_CASH_PLAN_DETAIL_BI	FACT_CASH_PLAN_DETAIL	Data Mart
CASH PROBABILITY	DIM_CASH_PROBABILITY_TM	DIM_CASH_PROBABILITY_BI	DIM_CASH_PROBABILITY	On Line
COMPANY	DIM_COMPANY_BI	DIM_COMPANY_BI	DIM_COMPANY	On Line
COMPANY STRUCTURE 1	DIM_COMP_STRUCT_1_TM	DIM_COMP_STRUCT_1_BI	DIM_COMP_STRUCT_1	On Line
COMPANY STRUCTURE 2	DIM_COMP_STRUCT_2_TM	DIM_COMP_STRUCT_2_BI	DIM_COMP_STRUCT_2	On Line
COMPANY STRUCTURE 3	DIM_COMP_STRUCT_3_TM	DIM_COMP_STRUCT_3_BI	DIM_COMP_STRUCT_3	On Line
COUNTERPART	DIM_CASH_PLAN_COUNTERPART_TM	DIM_CASH_PLAN_COUNTERPART_BI	DIM_CASH_PLAN_COUNTERPART	On Line
COUNTERPART STRUCTURE 1	DIM_COUNT_STRUCT_1_TM	DIM_COUNT_STRUCT_1_BI	DIM_COUNT_STRUCT_1	On Line
COUNTERPART STRUCTURE 2	DIM_COUNT_STRUCT_2_TM	DIM_COUNT_STRUCT_2_BI	DIM_COUNT_STRUCT_2	On Line
COUNTERPART STRUCTURE 3	DIM_COUNT_STRUCT_3_TM	DIM_COUNT_STRUCT_3_BI	DIM_COUNT_STRUCT_3	On Line
CURRENCY	DIM_CURRENCY_CODE_TM	DIM_CURRENCY_CODE_BI	DIM_CURRENCY_CODE	On Line
CUSTOMER	DIM_CUSTOMER_TM	DIM_CUSTOMER_BI	DIM_CUSTOMER	On Line
DIM_BI_TIME_BI	DIM_BI_TIME_BI	DIM_BI_TIME_BI	DIM_BI_TIME	On Line
INFLOW OUTFLOW	DIM_CASH_FLOW_IN_OUT_TM	DIM_CASH_FLOW_IN_OUT_BI	DIM_CASH_FLOW_IN_OUT	On Line
INTERNAL EXTERNAL CASH FLOW	DIM_CASH_FLOW_INT_EXT_TM	DIM_CASH_FLOW_INT_EXT_BI	DIM_CASH_FLOW_INT_EXT	On Line
PROJECT	DIM_PROJECT_TM	DIM_PROJECT_BI	DIM_PROJECT	On Line
PROJECT ACTIVITY	DIM_ACTIVITY_TM	DIM_ACTIVITY_BI	DIM_ACTIVITY	On Line
PROJECT COST BREAKDOWN STRUCTURE	DIM_PROJ_COST_BREAKDOWN_STRUCT_TM	DIM_PROJ_COST_BREAKDOWN_STRUCT_BI	DIM_PROJ_COST_BREAKDOWN_STRUCT	On Line
PROJECT COST REVENUE ELEMENT	DIM_COST_REV_ELEMENT_TM	DIM_COST_REV_ELEMENT_BI	DIM_COST_REV_ELEMENT	On Line
PROJECT PROGRAM	DIM_PROJECT_PROGRAM_TM	DIM_PROJECT_PROGRAM_BI	DIM_PROJECT_PROGRAM	On Line
PROJECT WORK BREAKDOWN STRUCTURE	DIM_PROJ_WORK_BREAKDOWN_STRUCT_TM	DIM_PROJ_WORK_BREAKDOWN_STRUCT_BI	DIM_PROJ_WORK_BREAKDOWN_STRUCT	On Line
SCENARIO	DIM_CASH_PLAN_SCENARIO_TM	DIM_CASH_PLAN_SCENARIO_BI	DIM_CASH_PLAN_SCENARIO	On Line
SNAPSHOT	DIM_CASH_PLAN_SNAPSHOT_TM	DIM_CASH_PLAN_SNAPSHOT_BI	DIM_CASH_PLAN_SNAPSHOT	On Line
SOURCE	DIM_CASH_PLAN_SOURCE_TM	DIM_CASH_PLAN_SOURCE_BI	DIM_CASH_PLAN_SOURCE	On Line
SUB CONTRACT	DIM_SUB_CONTRACT_TM	DIM_SUB_CONTRACT_BI	DIM_SUB_CONTRACT	On Line
SUB PROJECT	DIM_SUB_PROJECT_TM	DIM_SUB_PROJECT_BI	DIM_SUB_PROJECT	On Line
SUB SOURCE	DIM_CASH_PLAN_SUB_SOURCE_TM	DIM_CASH_PLAN_SUB_SOURCE_BI	DIM_CASH_PLAN_SUB_SOURCE	On Line
SUPPLIER	DIM_SUPPLIER_TM	DIM_SUPPLIER_BI	DIM_SUPPLIER	On Line
SWITCH CURRENCY TYPE
SWITCH MEASURE LIST CASH PLAN
SWITCH TAX
TIME BUCKET	DIM_CASH_PLAN_TIME_BUCKET_TM	DIM_CASH_PLAN_TIME_BUCKET_BI	DIM_CASH_PLAN_TIME_BUCKET	On Line
TIME SCALE	DIM_CASH_PLAN_TIME_SCALE_TM	DIM_CASH_PLAN_TIME_SCALE_BI	DIM_CASH_PLAN_TIME_SCALE	On Line
TRANSACTION TYPE	DIM_CASH_PLAN_TRANS_TYPE_TM	DIM_CASH_PLAN_TRANS_TYPE_BI	DIM_CASH_PLAN_TRANS_TYPE	On Line

Configurations

By defining company structures in IFS Cloud, dynamic out of the box analysis capabilities are provided. To achieve a consolidated view on node level, the node can be selected from the Company Structure and on the same time deselected from the Counterpart Structure.

The analysis can be performed in Transaction Currency, Accounting currency or in three common currencies called reporting currencies, defined from the Cash Plan page in IFS Cloud.

Limitations

The Cash planning model is using the following Project dimensions:

• Project
• Project activity
• Project Cost Breakdown Structure
• Project Cost Revenue Element
• Project Program
• Project Work Breakdown Structure
• Sub Project

Which means these seven dimensions need to be excluded from the model if the Project component is missing.

Relationships

The following table lists the relationships between tables and columns in the model.

Relationship: Table (column) - Table (column)

CASH PLAN CURRENCY (Cash Plan ID) - CASH PLAN (ID)

CASH PLAN CURRENCY (Switch Reporting Currency Row ID) - SWITCH CURRENCY TYPE (Row Index)

CASH PLAN DETAILS (Calender Date) - CASH FLOW PERIOD (ID)

CASH PLAN DETAILS (Dim_Accounting_Period_ID) - ACCOUNTING PERIOD (ID)

CASH PLAN DETAILS (Dim_Accounting_Project_ID) - ACCOUNTING PROJECT (ID)

CASH PLAN DETAILS (Dim_Acc_Currency_Code_ID) - ACCOUNTING CURRENCY (ID)

CASH PLAN DETAILS (Dim_Cash_Flow_Int_Ext_ID) - INTERNAL EXTERNAL CASH FLOW (ID)

CASH PLAN DETAILS (Dim_Cash_Flow_In_Out_ID) - INFLOW OUTFLOW (ID)

CASH PLAN DETAILS (Dim_Cash_Plan_Company_ID) - COMPANY (ID)

CASH PLAN DETAILS (Dim_Cash_Plan_Scenario_ID) - SCENARIO (ID)

CASH PLAN DETAILS (Dim_Cash_Plan_Source_ID) - SOURCE (ID)

CASH PLAN DETAILS (Dim_Cash_Plan_Sub_Source_ID) - SUB SOURCE (ID)

CASH PLAN DETAILS (Dim_Cash_Plan_Transaction_Type_ID) - TRANSACTION TYPE (ID)

CASH PLAN DETAILS (Dim_Cash_Probability_ID) - CASH PROBABILITY (ID)

CASH PLAN DETAILS (Dim_Company_ID) - COMPANY STRUCTURE 1 (Company Id)

CASH PLAN DETAILS (Dim_Company_ID) - COMPANY STRUCTURE 2 (Company Id)

CASH PLAN DETAILS (Dim_Company_ID) - COMPANY STRUCTURE 3 (Company Id)

CASH PLAN DETAILS (Dim_Counterpart_ID) - COUNTERPART (ID)

CASH PLAN DETAILS (Dim_Counterpart_ID) - COUNTERPART STRUCTURE 1 (Counterpart Id)

CASH PLAN DETAILS (Dim_Counterpart_ID) - COUNTERPART STRUCTURE 2 (Counterpart Id)

CASH PLAN DETAILS (Dim_Counterpart_ID) - COUNTERPART STRUCTURE 3 (Counterpart Id)

CASH PLAN DETAILS (Dim_Currency_Code_ID) - CURRENCY (ID)

CASH PLAN DETAILS (Dim_Customer_ID) - CUSTOMER (ID)

CASH PLAN DETAILS (Dim_Project_Activity_ID) - PROJECT ACTIVITY (ID)

CASH PLAN DETAILS (Dim_Project_Activity_ID) - PROJECT WORK BREAKDOWN STRUCTURE (ID)

CASH PLAN DETAILS (Dim_Project_Cost_Revenue_Element_ID) - PROJECT COST REVENUE ELEMENT (ID)

CASH PLAN DETAILS (Dim_Project_ID) - PROJECT (ID)

CASH PLAN DETAILS (Dim_Proj_Program_ID) - PROJECT PROGRAM (ID)

CASH PLAN DETAILS (Dim_Sub_Contract_Id) - SUB CONTRACT (ID)

CASH PLAN DETAILS (Dim_Sub_Project_ID) - SUB PROJECT (ID)

CASH PLAN DETAILS (Dim_Supplier_ID) - SUPPLIER (ID)

CASH PLAN DETAILS (Dim_Time_Bucket_ID) - TIME BUCKET (ID)

CASH PLAN DETAILS (Dim_Time_Scale_ID) - TIME SCALE (ID)

CASH PLAN DETAILS (Proj_Cost_Elem_ID) - PROJECT COST BREAKDOWN STRUCTURE (ID)

SCENARIO (SnapshotKey) - SNAPSHOT (ID)

SNAPSHOT (Cash Plan Id) - CASH PLAN (ID)

SWITCH MEASURE LIST CASH PLAN (Currency Type ID) - SWITCH CURRENCY TYPE (Row Index)

SWITCH MEASURE LIST CASH PLAN (Tax ID) - SWITCH TAX (Row Index)